/*
	EXEC SP_DETAILED_TRANSACTION_REPORT 10,'12/1/2008', '12/25/2008', 'Assembly', 'OSEP'
*/

ALTER PROC SP_DETAILED_TRANSACTION_REPORT  @BASE_PRODUCT_ID INT, 
										@FROM_DATE DATETIME, 
										@TO_DATE DATETIME,
										@PROCESS_TYPE VARCHAR(50),
										@VENDOR_NAME VARCHAR(50)
AS
BEGIN
	DECLARE @BASE_PRODUCT_ID_STR VARCHAR(10)
	DECLARE @DB_TABLE  TABLE ( 
 				PART_NUMBER VARCHAR(50),PROCESSSTEP_ID INT,
 				BEGINING_QTY INT, 
				IN_QTY INT,
				OUT_QTY INT,
				IN_FROM_FG INT, --TR
				IN_FROM_TR INT, --FG
				SHIPMENTS_TO_CUSTOMER INT, --FG
				SCRAP_QTY INT, 
				ENDING_QTY INT,
				DB_PART_NUMBER VARCHAR(50) ) --ASSEMLBY
	
	IF ( @BASE_PRODUCT_ID IS NULL OR @BASE_PRODUCT_ID <= 0 )
	BEGIN
		SET @BASE_PRODUCT_ID_STR = '%'
	END
	ELSE
	BEGIN
		SET @BASE_PRODUCT_ID_STR = CONVERT(VARCHAR, @BASE_PRODUCT_ID)
	END

	INSERT INTO @DB_TABLE(PART_NUMBER, PROCESSSTEP_ID)
	  SELECT P.PART_NUMBER, p.PROCESSSTEP_ID FROM Product P, Process_Step PS 
			WHERE  P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PS.PS_TYPE = @PROCESS_TYPE 
				AND P.P_PARENT_PRODUCT_ID LIKE @BASE_PRODUCT_ID_STR
	
	DECLARE @PROCESSSTEP_ID INT
	DECLARE @BEGINING_QTY INT
	DECLARE @ENDING_QTY INT
	DECLARE @IN_QTY INT
	DECLARE @OUT_QTY INT 
	DECLARE @IN_FROM_FG INT
	DECLARE @IN_FROM_TR INT
	DECLARE @SCRAP_QTY INT
	DECLARE @SHIPMENTS_TO_CUSTOMER INT
	DECLARE @TMP VARCHAR(20)
	DECLARE @DB_CURSOR CURSOR
	DECLARE @SEARCH_VENDOR_NAME VARCHAR(50)
	DECLARE @DB_PART_NUMBER VARCHAR(50)

	CREATE TABLE #BEGINING_WIP_RESULTS ( PROCESSSTEP_ID INT, LOCATION VARCHAR(50), LOT_NUMBER VARCHAR(50), FAB_LOT_NUMBER VARCHAR(50),
			LOT_CATEGORY VARCHAR(50), WAFER_QTY_IN INT, QTY_IN INT,	DATE_IN DATETIME, STAGE_NAME VARCHAR(50), 
			STAGE_POSITION VARCHAR(50), LOT_CURRENT_STATUS VARCHAR(50), EXPECTED_DATE_OUT DATETIME, AGE INT,
			OUTLOOK_DATE DATETIME, GROSS_DIE INT,WAFER_ID VARCHAR(200), 
			PART_NUMBER VARCHAR(50),BASE_PRODUCT VARCHAR(50), BASE_PRODUCT_ID INT,
			PS_TYPE VARCHAR(50),PS_TYPE_ID INT,THINKNESS VARCHAR(50),GRIND VARCHAR(50),
			SL_FLOW VARCHAR(50), SL_TEST_PROGRAM VARCHAR(50),SL_HOLD_DATE DATETIME,SL_TESTER VARCHAR(50),
			MASK VARCHAR(50),LABLE_NAME VARCHAR(50),PACKAGE_TYPE VARCHAR(50)
		 )
	CREATE TABLE #ENDING_WIP_RESULTS ( PROCESSSTEP_ID INT, LOCATION VARCHAR(50), LOT_NUMBER VARCHAR(50), FAB_LOT_NUMBER VARCHAR(50),
			LOT_CATEGORY VARCHAR(50), WAFER_QTY_IN INT, QTY_IN INT,	DATE_IN DATETIME, STAGE_NAME VARCHAR(50), 
			STAGE_POSITION VARCHAR(50), LOT_CURRENT_STATUS VARCHAR(50), EXPECTED_DATE_OUT DATETIME, AGE INT,
			OUTLOOK_DATE DATETIME, GROSS_DIE INT,WAFER_ID VARCHAR(200), 
			PART_NUMBER VARCHAR(50),BASE_PRODUCT VARCHAR(50), BASE_PRODUCT_ID INT,
			PS_TYPE VARCHAR(50),PS_TYPE_ID INT,THINKNESS VARCHAR(50),GRIND VARCHAR(50),
			SL_FLOW VARCHAR(50), SL_TEST_PROGRAM VARCHAR(50),SL_HOLD_DATE DATETIME,SL_TESTER VARCHAR(50),
			MASK VARCHAR(50),LABLE_NAME VARCHAR(50),PACKAGE_TYPE VARCHAR(50)
		 )
	DECLARE @BEGINIG_AS_OF_DATE DATETIME
	DECLARE @ENDING_AS_OF_DATE DATETIME
	SET @BEGINIG_AS_OF_DATE = DATEADD( ss, -1, @FROM_DATE )
	SET @ENDING_AS_OF_DATE = DATEADD( ss, -1, DATEADD( d, 1, @TO_DATE ) )
	--PRINT  @BEGINIG_AS_OF_DATE
	--PRINT  @ENDING_AS_OF_DATE

	IF ( CAST( CONVERT(VARCHAR,CURRENT_TIMESTAMP,101) AS DATETIME) <= CAST( CONVERT(VARCHAR,@BEGINIG_AS_OF_DATE,101) AS DATETIME) ) --CURRENT WIP
	BEGIN
		IF ( @PROCESS_TYPE <>'FG' )
		BEGIN
			INSERT INTO #BEGINING_WIP_RESULTS
				EXEC sp_wip_info_report @PROCESS_TYPE,'0',@VENDOR_NAME,'0','0','0','all','','0' --IN result set we have ps_type_name instead of ps_type, take care
		END
		ELSE --FOR FG WE ARE GETTING DIFFERENT RESULT SET
		BEGIN
			INSERT INTO #BEGINING_WIP_RESULTS(PROCESSSTEP_ID, LOCATION, LOT_NUMBER, FAB_LOT_NUMBER,
									 LOT_CATEGORY, QTY_IN, DATE_IN, LOT_CURRENT_STATUS, 
									STAGE_NAME, OUTLOOK_DATE, AGE, EXPECTED_DATE_OUT, 
									PART_NUMBER, WAFER_QTY_IN, SL_FLOW, PACKAGE_TYPE, SL_TESTER, PS_TYPE_ID)
				EXEC sp_wip_info_report @PROCESS_TYPE,'0',@VENDOR_NAME,'0','0','0','all','','0' --IN result set we have ps_type_name instead of ps_type, take care
		END
	END
	ELSE
	BEGIN
		SET @TMP = CONVERT(VARCHAR,@BEGINIG_AS_OF_DATE,101)
		IF ( @PROCESS_TYPE <>'FG' )
		BEGIN
			INSERT INTO #BEGINING_WIP_RESULTS
				EXEC sp_wip_info_as_of_date @PROCESS_TYPE,'0',@VENDOR_NAME,'0',@TMP,'0','all',''
		END
		ELSE
		BEGIN			

			INSERT INTO #BEGINING_WIP_RESULTS(PROCESSSTEP_ID, LOCATION, LOT_NUMBER, FAB_LOT_NUMBER,
									 LOT_CATEGORY, QTY_IN, DATE_IN, LOT_CURRENT_STATUS, 
									STAGE_NAME, OUTLOOK_DATE, AGE, EXPECTED_DATE_OUT, 
									PART_NUMBER, WAFER_QTY_IN, SL_FLOW, PACKAGE_TYPE, SL_TESTER, PS_TYPE_ID)
				EXEC sp_wip_info_as_of_date @PROCESS_TYPE,'0',@VENDOR_NAME,'0',@TMP,'0','all',''
		END
	END

	IF ( CAST( CONVERT(VARCHAR,CURRENT_TIMESTAMP,101) AS DATETIME) <= CAST( CONVERT(VARCHAR,@ENDING_AS_OF_DATE,101) AS DATETIME) ) --CURRENT WIP
	BEGIN
		IF ( @PROCESS_TYPE <>'FG' )
		BEGIN
			INSERT INTO #ENDING_WIP_RESULTS
				EXEC sp_wip_info_report @PROCESS_TYPE,'0',@VENDOR_NAME,'0','0','0','all','','0' --IN result set we have ps_type_name instead of ps_type, take care
		END
		ELSE
		BEGIN
			INSERT INTO #ENDING_WIP_RESULTS(PROCESSSTEP_ID, LOCATION, LOT_NUMBER, FAB_LOT_NUMBER,
									 LOT_CATEGORY, QTY_IN, DATE_IN, LOT_CURRENT_STATUS, 
									STAGE_NAME, OUTLOOK_DATE, AGE, EXPECTED_DATE_OUT, 
									PART_NUMBER, WAFER_QTY_IN, SL_FLOW, PACKAGE_TYPE, SL_TESTER, PS_TYPE_ID)
				EXEC sp_wip_info_report @PROCESS_TYPE,'0',@VENDOR_NAME,'0','0','0','all','','0' --IN result set we have ps_type_name instead of ps_type, take care
				
		END
	END
	ELSE
	BEGIN
		SET @TMP = CONVERT(VARCHAR,@ENDING_AS_OF_DATE,101)
		IF ( @PROCESS_TYPE <>'FG' )
		BEGIN
			INSERT INTO #ENDING_WIP_RESULTS
				EXEC sp_wip_info_as_of_date @PROCESS_TYPE,'0',@VENDOR_NAME,'0',@TMP,'0','all',''
		END
		ELSE
		BEGIN
			INSERT INTO #ENDING_WIP_RESULTS(PROCESSSTEP_ID, LOCATION, LOT_NUMBER, FAB_LOT_NUMBER,
									 LOT_CATEGORY, QTY_IN, DATE_IN, LOT_CURRENT_STATUS, 
									STAGE_NAME, OUTLOOK_DATE, AGE, EXPECTED_DATE_OUT, 
									PART_NUMBER, WAFER_QTY_IN, SL_FLOW, PACKAGE_TYPE, SL_TESTER, PS_TYPE_ID)
				EXEC sp_wip_info_as_of_date @PROCESS_TYPE,'0',@VENDOR_NAME,'0',@TMP,'0','all',''
		END
	END
	IF ( @VENDOR_NAME IS NULL OR @VENDOR_NAME = '0' OR @VENDOR_NAME = '' )
	BEGIN
		SET @SEARCH_VENDOR_NAME = '%'
	END
	ELSE
	BEGIN
		SET @SEARCH_VENDOR_NAME = @VENDOR_NAME
	END
	SET @DB_CURSOR = CURSOR FOR SELECT PROCESSSTEP_ID FROM @DB_TABLE
	OPEN @DB_CURSOR 
	FETCH NEXT FROM @DB_CURSOR INTO @PROCESSSTEP_ID
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT @BEGINING_QTY = ISNULL( SUM ( QTY_IN ), 0 ) FROM  #BEGINING_WIP_RESULTS WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID-- AND BASE_PRODUCT_ID LIKE @BASE_PRODUCT_ID_STR
		SELECT @ENDING_QTY = ISNULL( SUM ( QTY_IN ), 0 ) FROM  #ENDING_WIP_RESULTS WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID-- AND BASE_PRODUCT_ID LIKE @BASE_PRODUCT_ID_STR
		SELECT @SCRAP_QTY = SUM( SL.SL_SCRAPPED_DIE_QTY ) FROM SCRAPPED_LOTS SL, LOT_TABLE LT 
							WHERE SL.LOT_ID = LT.LOT_ID 
								AND LT.PROCESSSTEP_ID = @PROCESSSTEP_ID 
								AND CAST(CONVERT(VARCHAR,SL.SL_SCRAPPED_DATE,101) AS DATETIME) >= @FROM_DATE 
								AND CAST(CONVERT(VARCHAR,SL.SL_SCRAPPED_DATE,101) AS DATETIME) <= @TO_DATE  			
		IF ( @PROCESS_TYPE = 'Die Bank' )
		BEGIN
			SELECT @IN_QTY=SUM(QTY_IN) FROM LOT_TABLE LT, PROCESS_STEP PS, LOCATION L
			WHERE PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND LT.PROCESSSTEP_ID = @PROCESSSTEP_ID
				AND LT.FAB_LOT_NUMBER IN (
				SELECT DISTINCT FAB_LOT_NUMBER FROM SHIPPED_LOT SL, LOT_TABLE LT
						WHERE LT.LOT_ID = SL.LOT_ID AND SL.STATUS = 'CLOSE' 
						AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) >= @FROM_DATE AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) <= @TO_DATE
						AND LT.PROCESSSTEP_ID IN ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOW_BY_PROCESSSTEP_ID ( @PROCESSSTEP_ID ) WHERE PROCESS_TYPE_NAME = 'FAB' )
							)
				AND L.LOCATION_ID = LT.LOCATION_ID
				AND L.L_VENDOR_NAME LIKE @SEARCH_VENDOR_NAME

			SELECT @OUT_QTY = SUM(SHIP_QTY) FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS, LOCATION L
					WHERE LT.LOT_ID = SL.LOT_ID AND SL.STATUS = 'CLOSE' 
					AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
					AND LT.ProcessStep_ID = @PROCESSSTEP_ID
					AND SL.SL_NEXT_PROCESS <> 'DIE BANK' 
					AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) >= @FROM_DATE AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) <= @TO_DATE
					AND L.LOCATION_ID = LT.LOCATION_ID
					AND L.L_VENDOR_NAME LIKE @SEARCH_VENDOR_NAME
		END
		ELSE IF ( @PROCESS_TYPE = 'Assembly' )
		BEGIN 
			SELECT @DB_PART_NUMBER = PART_NUMBER FROM PRODUCT P, PROCESS_STEP_TRAVERSE_TABLE PSTT, PROCESS_STEP PS
										WHERE P.PROCESSSTEP_ID = PSTT.PROCESSSTEP_ID
											AND PS.PROCESSSTEP_ID = P.PROCESSSTEP_ID
											AND PSTT.NEXT_PROCESSSTEP_ID = @PROCESSSTEP_ID
											AND PS.PS_TYPE_NAME = 'Die Bank'

			SELECT @IN_QTY = SUM(SHIP_QTY) FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS, LOCATION L
				WHERE LT.LOT_ID = SL.LOT_ID AND SL.STATUS = 'CLOSE' 
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND PS.PS_TYPE = 'Die Bank'
				AND SL.SL_NEXT_PROCESS <> 'DIE BANK' 
				AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) >= @FROM_DATE AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) <= @TO_DATE
				AND LT.LOT_NUMBER IN (SELECT DISTINCT SHIPPED_LOT_NUMBER FROM ASSM_RELEASE_TABLE ART, Assm_Release_Lot_Table ARLT 
						WHERE ART.ASSEMBLY_RELEASE_ID = ARLT.ASSEMBLY_RELEASE_ID
							AND ART.STATUS <> 'CANCELLED'
							AND ART.PROCESSSTEP_ID = @PROCESSSTEP_ID)
				AND L.LOCATION_ID = LT.LOCATION_ID
				AND L.L_VENDOR_NAME LIKE @SEARCH_VENDOR_NAME

			SELECT @OUT_QTY = SUM(SHIP_QTY) FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS, LOCATION L
				WHERE LT.LOT_ID = SL.LOT_ID AND SL.STATUS = 'CLOSE'
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND PS.PS_TYPE = 'ASSEMBLY'
				AND SL.SL_NEXT_PROCESS <> 'ASSEMBLY' 
				AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) >= @FROM_DATE AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) <= @TO_DATE
				AND LT.PROCESSSTEP_ID = @PROCESSSTEP_ID
				AND L.LOCATION_ID = LT.LOCATION_ID
				AND L.L_VENDOR_NAME LIKE @SEARCH_VENDOR_NAME

		END
		ELSE IF ( @PROCESS_TYPE = 'FT' )
		BEGIN
			SELECT @IN_QTY = SUM(SHIP_QTY) FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS
				WHERE LT.LOT_ID = SL.LOT_ID AND SL.STATUS = 'CLOSE' 
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND SL.SL_NEXT_PROCESS = 'FT' 
				AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) >= @FROM_DATE AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) <= @TO_DATE
				AND LT.PROCESSSTEP_ID IN ( SELECT ProcessStep_Id FROM FN_GET_PROCESS_FLOW_BY_PROCESSSTEP_ID ( @PROCESSSTEP_ID ) WHERE PROCESS_TYPE_NAME = 'Assembly' )
				AND SL.SL_NEXTLOCATION LIKE @SEARCH_VENDOR_NAME

			SELECT @OUT_QTY = SUM(SHIP_QTY) FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS, LOCATION L
				WHERE LT.LOT_ID = SL.LOT_ID AND SL.STATUS = 'CLOSE' 
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND PS.PS_TYPE = 'FT'
				AND SL.SL_NEXT_PROCESS <> 'FT'
				AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) >= @FROM_DATE AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) <= @TO_DATE
				AND LT.PROCESSSTEP_ID = @PROCESSSTEP_ID
				AND L.LOCATION_ID = LT.LOCATION_ID
				AND L.L_VENDOR_NAME LIKE @SEARCH_VENDOR_NAME
		END
		ELSE IF ( @PROCESS_TYPE = 'TAPE & REEL' )
		BEGIN
			DECLARE @RECEIVED_FROM_FG_QTY INT
			DECLARE @RECEIVED_FROM_FFG_QTY INT

			SELECT @IN_QTY = SUM(SHIP_QTY) FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS--, PRODUCT P  
				WHERE LT.LOT_ID = SL.LOT_ID AND SL.STATUS = 'CLOSE'
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND SL.SL_NEXT_PROCESS = 'Tape & Reel' 
				AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) >= @FROM_DATE AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) <= @TO_DATE
				AND LT.PROCESSSTEP_ID IN (SELECT DISTINCT PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID(@PROCESSSTEP_ID,0) WHERE PS_TYPE_NAME = 'TEST' )
				AND SL.SL_NEXTLOCATION LIKE @SEARCH_VENDOR_NAME

			SELECT @RECEIVED_FROM_FG_QTY = SUM(SHIP_QTY) FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS--, PRODUCT P  
				WHERE LT.LOT_ID = SL.LOT_ID AND SL.STATUS = 'CLOSE'
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND SL.SL_NEXT_PROCESS = 'Tape & Reel' 
				AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) >= @FROM_DATE AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) <= @TO_DATE
				AND LT.PROCESSSTEP_ID IN (SELECT DISTINCT PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID(@PROCESSSTEP_ID,0) WHERE PS_TYPE_NAME = 'FINISHED GOODS')
				AND SL.SL_NEXTLOCATION LIKE @SEARCH_VENDOR_NAME

			
			SELECT @RECEIVED_FROM_FFG_QTY = SUM(SHIP_QTY) FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS
				WHERE LT.LOT_ID = SL.LOT_ID AND SL.STATUS = 'CLOSE'
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND SL.SL_NEXT_PROCESS = 'Tape & Reel' 
				AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) >= @FROM_DATE AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) <= @TO_DATE
				AND LT.PROCESSSTEP_ID IN (SELECT DISTINCT PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID(@PROCESSSTEP_ID,1) WHERE PS_TYPE_NAME = 'FINISHED GOODS')
				AND SL.SL_NEXTLOCATION LIKE @SEARCH_VENDOR_NAME


			SET @IN_FROM_FG = ISNULL(@RECEIVED_FROM_FG_QTY,0) + ISNULL(@RECEIVED_FROM_FFG_QTY,0)

			SELECT @OUT_QTY = SUM(SHIP_QTY) FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS, LOCATION L
				WHERE LT.LOT_ID = SL.LOT_ID AND SL.STATUS = 'CLOSE' 
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND PS.PS_TYPE = 'Tape & Reel'
				AND SL.SL_NEXT_PROCESS <> 'Tape & Reel'
				AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) >= @FROM_DATE AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) <= @TO_DATE
				AND LT.PROCESSSTEP_ID = @PROCESSSTEP_ID	
				AND L.LOCATION_ID = LT.LOCATION_ID
				AND L.L_VENDOR_NAME LIKE @SEARCH_VENDOR_NAME
		END
		ELSE IF ( @PROCESS_TYPE = 'FG' )
		BEGIN
			SELECT @IN_QTY = SUM(SHIP_QTY) FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS
				WHERE LT.LOT_ID = SL.LOT_ID AND SL.STATUS = 'CLOSE' 
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND SL.SL_NEXT_PROCESS = 'FG' 
				AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) >= @FROM_DATE AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) <= @TO_DATE
				AND LT.PROCESSSTEP_ID IN ( SELECT ProcessStep_Id FROM FN_GET_PROCESS_FLOW_BY_PROCESSSTEP_ID ( @PROCESSSTEP_ID ) WHERE PROCESS_TYPE_NAME = 'TEST' )
				AND SL.SL_NEXTLOCATION LIKE @SEARCH_VENDOR_NAME

			SELECT @IN_FROM_TR = SUM(SHIP_QTY) FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS
				WHERE LT.LOT_ID = SL.LOT_ID AND SL.STATUS = 'CLOSE' 
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND SL.SL_NEXT_PROCESS = 'FG' 
				AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) >= @FROM_DATE AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) <= @TO_DATE
				AND LT.PROCESSSTEP_ID IN ( SELECT ProcessStep_Id FROM FN_GET_PROCESS_FLOW_BY_PROCESSSTEP_ID ( @PROCESSSTEP_ID ) WHERE PROCESS_TYPE_NAME = 'CLASS STORE' )
				AND SL.SL_NEXTLOCATION LIKE @SEARCH_VENDOR_NAME

			SELECT @OUT_QTY = SUM(SHIP_QTY) FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS, LOCATION L
				WHERE LT.LOT_ID = SL.LOT_ID AND SL.STATUS = 'CLOSE' 
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND PS.PS_TYPE = 'FG'
				AND SL.SL_NEXT_PROCESS <> 'FG'
				AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) >= @FROM_DATE AND CAST(CONVERT(VARCHAR,ISNULL(SL.CLOSE_DATE,SL.SHIP_DATE),101) AS DATETIME) <= @TO_DATE
				AND LT.PROCESSSTEP_ID = @PROCESSSTEP_ID	
				AND L.LOCATION_ID = LT.LOCATION_ID
				AND L.L_VENDOR_NAME LIKE @SEARCH_VENDOR_NAME

			SELECT @SHIPMENTS_TO_CUSTOMER = SUM(CSLNT_QTY_PULL)
				FROM CUSTOMER_SHIPPING_TABLE CST, 
					CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT,
					CUSTOMER_SHIPPING_LOT_NUMBER_TABLE CSLNT, 
					CUSTOMER_OPEN_ORDER_LINEITEM COOL, 
					PRODUCT P,
					LOT_TABLE LT,
					LOCATION L
				WHERE CSLIT.CUSTOMERSHIPPING_ID = CST.CUSTOMERSHIPPING_ID 
					AND CST.CST_STATUS = 'SHIPPED'
					AND CSLNT.CSLIT_ID = CSLIT.CSLIT_ID
					AND COOL.LINEITEM_ID = CSLIT.LINEITEM_ID		
					AND P.PRODUCT_ID = COOL.PRODUCT_ID	
					AND LT.LOT_ID = CSLNT.LOT_ID	
					AND CAST(CONVERT(VARCHAR,CST.CST_CONFIRMED_ON,101) AS DATETIME) >= @FROM_DATE 
					AND CAST(CONVERT(VARCHAR,CST.CST_CONFIRMED_ON,101) AS DATETIME) <= @TO_DATE
					AND P.PROCESSSTEP_ID = @PROCESSSTEP_ID
					AND CSLNT_QTY_PULL > 0
					AND L.LOCATION_ID = LT.LOCATION_ID
					AND L.L_VENDOR_NAME LIKE @SEARCH_VENDOR_NAME
		END
		UPDATE @DB_TABLE SET BEGINING_QTY = @BEGINING_QTY, ENDING_QTY = @ENDING_QTY, IN_QTY = @IN_QTY, OUT_QTY = @OUT_QTY, SCRAP_QTY = @SCRAP_QTY, IN_FROM_FG = @IN_FROM_FG, IN_FROM_TR = @IN_FROM_TR, SHIPMENTS_TO_CUSTOMER = @SHIPMENTS_TO_CUSTOMER, DB_PART_NUMBER = @DB_PART_NUMBER WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID
		SET @BEGINING_QTY = 0
		SET @ENDING_QTY = 0
		SET @IN_QTY = 0
		SET @OUT_QTY = 0
		SET @SCRAP_QTY = 0
		SET @IN_FROM_FG = 0
		SET @IN_FROM_TR = 0
		SET @SHIPMENTS_TO_CUSTOMER = 0
		SET @DB_PART_NUMBER = NULL
		FETCH NEXT FROM @DB_CURSOR INTO @PROCESSSTEP_ID
	END
	CLOSE @DB_CURSOR
	DEALLOCATE @DB_CURSOR

	SELECT * FROM @DB_TABLE
END